package com.wd.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.atomic.AtomicInteger;
/**
 * @author long
 * @date 2013-5-2 上午11:48:03
 * @qq 108232706
 * @description TestDbPool
 */
public class TestDbPool implements Runnable /* extends Thread */{
	public long date1 = 0;
	private static Proxool proxool;
	public static AtomicInteger count = new AtomicInteger(0);
	public static int threadCount = 1;
	public static final DBMgr[] dbMgr = new DBMgr[threadCount];

	int addCount = 10000;
	public int dbType;

	/*
	 *
	 * 1---->BoneCPConn-0 PreparedStatement tb2 addCount:10000 cost ms:16947
	 * 1---->dbMgr-0 PreparedStatement tb2 addCount:10000 cost ms:17088
	 * 1---->dbMgr-0 Statement addBatch tb2 addCount:10000 cost ms:8584
	 * 1---->dbMgr-0 Statement addBatch tb2 addCount:10000 cost ms:8590
	 * 1---->BoneCPConn-0 Statement addBatch tb2 addCount:10000 cost ms:9762
	 * 1---->BoneCPConn-0 Statement addBatch tb2 addCount:10000 cost ms:10067
	 * 1---->BoneCPConn-0 Statement addBatch tb2 addCount:10000 cost ms:8280
	 * 1---->BoneCPConn-0 Statement addBatch tb2 addCount:10000 cost ms:8424(主从)
	 * 1---->BoneCPConn-0 Statement addBatch tb2 addCount:10000 cost ms:11138(主从)
	 * 1---->BoneCPConn-0 Statement addBatch tb2 addCount:10000 cost ms:8937(主从)
	 * 1---->BoneCPConn-0 Statement addBatch tb2 addCount:10000 cost ms:8847
	 * 1---->BoneCPConn-0 PreparedStatement prepareAddBatch tb2 addCount:10000 cost ms:16799
	 * 1---->BoneCPConn-0 PreparedStatement prepareAddBatch tb2 addCount:10000 cost ms:16218
	 */
	public static void main(String[] args) throws Exception {
		// DBCP.init();
		// C3P0.init();
		// proxool = Proxool.getInstance();
		// BoneCPConn.init();
		// for (int i = 1; i <= 4; i++) {
		// DBTest test = new DBTest(i);
		// test.startup();
		// }
		TestDbPool test = new TestDbPool(4);
		test.startup();

	}

	public void startup() {
		for (int i = 0; i < threadCount; i++) {
			Thread thread = new Thread(this);
			thread.setName(getName() + "-" + i);
			thread.start();
		}
	}

	public TestDbPool(int _dbType) {
		dbType = _dbType;
		init();
	}

	public void init() {
		switch (dbType) {
		case 1:
			DBCP.init();
			break;
		case 2:
			C3P0.init();
			break;
		case 3:
			proxool = Proxool.getInstance();
		case 4:
			BoneCPConn.init();
			break;
		case 5:
			for (int i = 0; i < dbMgr.length; i++) {
				try {
					dbMgr[i] = new DBMgr(i);
				} catch (Throwable e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			break;
		default:
			;
		}
	}

	public Connection open() throws SQLException {
		int index = Integer.valueOf(Thread.currentThread().getName().split("-")[1]);
		System.out.println(Thread.currentThread().getName() + "open:" + index);
		switch (dbType) {
		case 1:
			return DBCP.getConnection();
		case 2:
			return C3P0.getConnection();
		case 3:
			return proxool.getConnection();
		case 4:
			return BoneCPConn.getConnection();
		case 5:
			return dbMgr[index].Open();
		default:
			return null;
		}
	}

	public String getName() {
		switch (dbType) {
		case 1:
			return "DBCP";
		case 2:
			return "C3P0";
		case 3:
			return "proxool";
		case 4:
			return "BoneCPConn";
		case 5:
			return "dbMgr";
		default:
			return "";
		}
	}

	public void testBatchInsertTb1(int index) throws SQLException {
		 long start = System.currentTimeMillis();
		Connection conn = open();
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String account = Thread.currentThread().getName();
		try {
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(" insert into tb2(num,account)values(?,'" + account + "')", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < addCount; i++) {
				prepareAddBatch(pstmt, i);
			}
			pstmt.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null) {
				pstmt.clearBatch();
				pstmt.close();

			}
			if (dbType != 5)
				conn.close();
			System.out.println(threadCount+"---->"+Thread.currentThread().getName()+" PreparedStatement prepareAddBatch tb2 addCount:" +addCount +" cost ms:" + (System.currentTimeMillis()-start));
		}
	}

	public void testBatchInsertTb2(int index) throws SQLException {
		 long start = System.currentTimeMillis();
		Connection conn = open();
		ResultSet rs = null;
		Statement pstmt = null;
		String account = Thread.currentThread().getName();
		try {
			conn.setAutoCommit(false);
			pstmt = conn.createStatement();
			for (int i = 0; i < addCount; i++) {
				pstmt.addBatch(" insert into tb2(num,account)values(" + i + ",'" + account + "')");
			}
			pstmt.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null) {
				pstmt.clearBatch();
				pstmt.close();

			}
			if (dbType != 5)
				conn.close();
			System.out.println(threadCount+"---->"+Thread.currentThread().getName()+" Statement addBatch tb2 addCount:" +addCount +" cost ms:" + (System.currentTimeMillis()-start));
		}
	}

	protected void prepareAddBatch(PreparedStatement pstmt, Object... parms) throws SQLException {
		if ((pstmt == null) || (parms == null))
			return;
		for (int i = 1; i <= parms.length; i++) {
			pstmt.setObject(i, parms[i - 1]);
		}
		pstmt.addBatch();
	}

	public void run() {
		/*
		 * String currThreadName = Thread.currentThread().getName();
		 * System.out.println(currThreadName+"---");
		 */
		/*
		 * if(currThreadName.equals("Thread-0")) { date1 =
		 * System.currentTimeMillis(); System.out.println(date1); }
		 */
		if (count.get() == 0 && date1 == 0) {
			date1 = System.currentTimeMillis();
			System.out.println(date1);
		}

		/*
		 * for (int i = 0; i < 10; i++) { try { // Connection conn =
		 * DBCP.getConnection(); // Connection conn = C3P0.getConnection(); //
		 * Connection conn =proxool.getConnection(); // Connection conn =
		 * BoneCPConn.getConnection(); Connection conn = open(); if (conn !=
		 * null) { Statement statement = conn.createStatement(); ResultSet rs =
		 * statement .executeQuery("select * from tb1 where idx=1"); while
		 * (rs.next()) { java.sql.Timestamp username = rs.getTimestamp("ct"); //
		 * System.out.println(username); } rs.close(); statement.close(); //
		 * conn.close(); } } catch (SQLException e) { e.printStackTrace(); } }
		 */

		try {
			testBatchInsertTb2(0);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		/*
		 * if (currThreadName.equals("Thread-4")) { long date2 =
		 * System.currentTimeMillis(); System.out.println(date2);
		 * System.out.println("运行完毕!耗时为：" + (date2 - date1) + "ms"); }
		 */
		count.addAndGet(1);
		System.out.println("thread-" + getName() + count + " exit");
		if (count.get() == threadCount) {
			long date2 = System.currentTimeMillis();
			System.out.println(date2);
			System.out.println(getName() + " testBatchInsertTb2 addCount:" + addCount + " 并发线程数:" + threadCount + "======>运行完毕!耗时为：" + (date2 - date1) + "ms");
		}
	}
}